﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Collections.Generic;
using DapperEx;
using System.Configuration;
using System.Data.Common;
using System.Linq;
using Dapper;
using CSSD.Server.DataModel.ManagerBenchModel;
using System.Data;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Collections;
using System.Linq.Expressions;
using System.Reflection.Emit;
using System.Diagnostics;
using CSSD.Server.DataModel.RoutineBusinessModel;
using ExtensionHelper;

namespace ExtensionHelper
{
    [TestClass]
    public class UnitTest1
    {
        public string connectionName = "strSqlCe";
        public string conn = "LocalSqlServer1";
        public string conn_Test = "strSqlCe_DbTest";

        [TestMethod]
        public void TestDB()
        {
            IEnumerable<dynamic> list = new DbBase(conn_Test).DbConnecttion.Query("");
        }

        public DbBase CreateDbBase()
        {
            return new DbBase(conn);
        }
        [TestMethod]
        public void Connection()
        {
            var providerName = "System.Data.SqlClient";
            var connStr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            if (!string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[connectionName].ProviderName))
                providerName = ConfigurationManager.ConnectionStrings[connectionName].ProviderName;

            var dbProvider = DbProviderFactories.GetFactory(providerName);
            var conn = dbProvider.CreateConnection();
            conn.ConnectionString = connStr;
            conn.Open();
            Console.WriteLine("状态：" + conn.State.ToString());
        }
        [TestMethod]
        public void Insert()//插入一条数据
        {
            string WhereSql = "";
            int TopNum = 100;
            string OrderSql = " order by ss";
            var strWhere = "";
            if (string.IsNullOrEmpty(WhereSql))
            {
                strWhere = string.Format(" and  ROWNUM <= {0} ", TopNum);
            }
            else
            {
                strWhere = string.Format(" {0} AND ROWNUM <= {1} ", WhereSql, TopNum);
            }
            string sqlStr = string.Format("SELECT * FROM {0} where 1=1 {1} {2}", "aaa", strWhere, OrderSql);


            ItemCategoryModel model = new ItemCategoryModel();
            model.ItemCategoryName = "5";
            model.CreatedDatetime = DateTime.Now;
            model.ArchivedDatetime = DateTime.Now;
            //model.DeliveryPointID = 1;
            model.CustID = "1";
            model.Reversion = "100000";
            model.AccountCode = "account";
            model.SpellCode = "111";
            model.IsStop = 1;
            model.IsEnabled = 1;
            model.IsPrintDetail = 1;
            model.ItemCategoryCode = "aaaaaa";
            model.BarcodeFlag = "1";
            int ret = CreateDbBase().InsertAndReturnID<ItemCategoryModel>(model);

            var model1 = new Account()
            {
                Id = "12",
                Name = "张三2",
                Password = "123456",
                Email = "123@qq.com",
                CreateTime = DateTime.Now,
                Age = 15
            };
            using (var db = CreateDbBase())
            {
                var result = db.Insert<Account>(model1);
                Assert.IsTrue(result);
                if (result)
                    Console.WriteLine("添加成功");
                else
                    Console.WriteLine("添加失败");
            }
        }
        [TestMethod]
        public void Insert2()//插入一条数据
        {
            for (int i = 2; i < 10; i++)
            {
                var model = new Account()
                {
                    Id = i.ToString(),
                    Name = "张三" + i.ToString(),
                    Password = "123456",
                    Email = "123@qq.com",
                    CreateTime = DateTime.Now,
                    Age = 15
                };
                using (var db = CreateDbBase())
                {
                    var result = db.Insert<Account>(model);
                }
            }
        }
        [TestMethod]
        public void Insert3()//插入一条数据
        {
            using (var db = CreateDbBase())
            {
                var tran = db.DbTransaction;
                for (int i = 2; i < 10; i++)
                {
                    var model = new Account()
                    {
                        Id = i.ToString(),
                        Name = "张三" + i.ToString(),
                        Password = "123456",
                        Email = "123@qq.com",
                        CreateTime = DateTime.Now,
                        Age = 15
                    };

                    var result = db.Insert<Account>(model, tran);

                }
                tran.Commit();
            }
        }
        [TestMethod]
        public void InsertBatch()//插入多条数据
        {
            int count = 1;
            List<ItemCategoryModel> list1 = new List<ItemCategoryModel>();
            for (int i = 0; i < count; i++)
            {
                ItemCategoryModel model = new ItemCategoryModel();
                model.ItemCategoryName = "5";
                model.CreatedDatetime = DateTime.Now;
                model.ArchivedDatetime = DateTime.Now;
                model.DeliveryPointID = 1;
                model.CustID = "1";
                model.Reversion = "100000";
                model.AccountCode = "account";
                model.SpellCode = "111";
                model.IsStop = 1;
                model.IsEnabled = 1;
                model.IsPrintDetail = 1;
                model.ItemCategoryCode = "aaaaaa";
                model.BarcodeFlag = "1";
                list1.Add(model);
            }
            CreateDbBase().InsertBatchDt<ItemCategoryModel>(list1.ListToDataTable<ItemCategoryModel>());
            var list = new List<Account>();
            for (int i = 10; i < 21; i++)
            {
                var model = new Account()
                {
                    Id = i.ToString(),
                    Name = "张三" + i.ToString(),
                    Password = "123456",
                    Email = "123@qq.com",
                    CreateTime = DateTime.Now,
                    Age = 15
                };
                list.Add(model);
            }
            using (var db = CreateDbBase())
            {
                var result = db.InsertBatch<Account>(list);
                if (result)
                    Console.WriteLine("添加成功");
                else
                    Console.WriteLine("添加失败");
            }
        }
        [TestMethod]
        public void InsertBatchTest()//插入多条数据
        {
            int count = 1000;
            var list = new List<Account>();
            for (int i = 0; i < count; i++)
            {
                var model = new Account()
                {
                    Name = "张三" + i.ToString(),
                    Password = "123456",
                    Email = "123@qq.com",
                    CreateTime = DateTime.Now,
                    Age = 15
                };
                list.Add(model);
            }

            var result = CreateDbBase().InsertBatch<Account>(list);
            if (result)
                Console.WriteLine("添加成功");
            else
                Console.WriteLine("添加失败");
        }
        [TestMethod]
        public void QueryNoWhere()//无条件的查询,相当于GetAll
        {
            using (var db = CreateDbBase())
            {
                var result = db.Query<CSSD.Server.DataModel.ManagerBenchModel.AccountCodeModel>();
                Console.WriteLine("查询出数据条数:" + result.Count());
            }
        }
        [TestMethod]
        public void TestMultiQuery()
        {
            string sql = @"select * from UserGroupMenu where UserGroupMenuID in @UserGroupMenuID;
                           select * from Menu where MenuID in @MenuID";


            var list1 = CreateDbBase().DbConnecttion.Query(sql).Select(info => new { user = "" }).ToList();


            var result = CreateDbBase().DbConnecttion.QueryMultiple(sql, new { UserGroupMenuID = new int[] { 1, 2 }, MenuID = new int[] { 1, 2 } });
            List<UserGroupMenuModel> userGroupMenuModelList = result.Read<UserGroupMenuModel>().ToList();
            List<MenuModel> menuModelList = result.Read<MenuModel>().ToList();


            DataTable dt = CreateDbBase().DbConnecttion.QueryDataTable(@"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
                    and UserGroupID in (" + 14 + ") and m.MenuType = 1");


            //            List<UserGroupMenuModel> vList = CreateDbBase().DbConnecttion.Query<UserGroupMenuModel, MenuModel, UserGroupMenuModel>(@"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
            //                    and UserGroupID in (" + 14 + ") and m.MenuType = 1", (userGroupMenu, menu) => { return userGroupMenu; }, splitOn: "MenuID").ToList();




            var result1 = CreateDbBase().DbConnecttion.QueryMultiple(@"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
                    and UserGroupID in (" + 14 + ") and m.MenuType = 1");
            List<UserGroupMenuModel> userGroupMenuModelList1 = result1.Read<UserGroupMenuModel>().ToList();



            List<UserGroupModel> list = CreateDbBase().DbConnecttion.Query<UserGroupModel, MenuModel, UserGroupModel>(@"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
                    and UserGroupID in (" + 14 + ") and m.MenuType = 1", (userGroup, menu) => { return userGroup; }, splitOn: "MenuID").ToList();

            //UserGroupModel s = list[0];
        }
        [TestMethod]
        public void QueryWhereSQL()//无条件的查询,相当于GetAll
        {
            var result1 = CreateDbBase().Query<Account>(" and id=@id", new { id = 1 });
            var result2 = CreateDbBase().Query<Account>(" and id in @id", new { id = new int[] { 1, 2 } });
            var result3 = CreateDbBase().Query<Account>(" and Name like @Name", new { Name = "%1%" });
            var result = CreateDbBase().Query<Account>(" and id=2");
            Console.WriteLine("查询出数据条数:" + result.Count());
        }
        [TestMethod]
        public void Query()//条件查询
        {
            using (var db = CreateDbBase())
            {
                var d = SqlQuery<Account>.Builder(db).AndWhere(m => m.Age, OperationMethod.Less, 20)
                     .LeftInclude()//此表示左括号，所以后面必须有右括号与之对应
                     .AndWhere(m => m.CreateTime, OperationMethod.Greater, DateTime.Now.AddDays(-5))
                     .AndWhere(m => m.CreateTime, OperationMethod.LessOrEqual, DateTime.Now.AddDays(5))
                     .OrWhere(m => m.Name, OperationMethod.Contains, "张")
                     .RightInclude()//右括号
                     .Top(10)//前10条
                     .AndWhere(m => m.Age, OperationMethod.In, new List<int>() { 15 })
                     .OrderBy(m => m.Age, true);
                //WHERE Age < @para_1 AND ( CreateTime > @para_2 OR Name LIKE @para_3 ) AND Age IN @para_4 ORDER BY Age DESC
                var result = db.Query<Account>(d);
                Console.WriteLine("查询出数据条数:" + result.Count());
            }
        }
        [TestMethod]
        public void Page()//分布查询
        {
            long count = 0;

            IList<ItemCategoryModel> list = CreateDbBase().PageByProc<ItemCategoryModel>("ItemCategory", 12, 20, out count, "ItemCategoryID", " and isstop=0", System.Data.SqlClient.SortOrder.Descending).ToList();

            using (var db = CreateDbBase())
            {
                var d = SqlQuery<ItemCategoryModel>.Builder(db).AndWhere(m => m.ItemCategoryID, OperationMethod.Less, 20)
                     .LeftInclude()
                     .AndWhere(m => m.CreatedDatetime, OperationMethod.Greater, DateTime.Now.AddDays(-5))
                     .AndWhere(m => m.ItemCategoryName, OperationMethod.Contains, "张")
                     .RightInclude()
                     .AndWhere(m => m.ItemCategoryID, OperationMethod.In, new List<int>() { 15 })
                     ;
                long dc = 0;
                var result = db.Page<ItemCategoryModel>(1, 20, out dc, d);
                Console.WriteLine("查询出数据条数:" + result.Count());
            }
        }

        [TestMethod]
        public void Delete()//删除测试
        {
            using (var db = CreateDbBase())
            {

                var d = SqlQuery<Account>.Builder(db)
                    .AndWhere(m => m.Id, OperationMethod.Equal, "1");
                var result = db.Delete<Account>(d);
            }
        }
        [TestMethod]
        public void Delete2() //删除测试
        {
            using (var db = CreateDbBase())
            {

                var d = SqlQuery<Account>.Builder(db)
                    .AndWhere(m => m.Id, OperationMethod.In, new List<string>() { "2", "3" });
                var result = db.Delete<Account>(d);
            }
        }
        [TestMethod]
        public void DeleteWhereSQL() //删除测试
        {
            bool ret1 = CreateDbBase().Delete<Account>(" and id in @id", new { id = new int[] { 1, 2 } });
            bool ret2 = CreateDbBase().Delete<Account>(" and id in (1,2)");
            Assert.IsTrue(ret2);
        }
        [TestMethod]
        public void Single()//获取一条数据
        {
            using (var db = CreateDbBase())
            {
                var model = db.SingleOrDefault<Account>(SqlQuery<Account>.Builder(db)
                    .AndWhere(m => m.Id, OperationMethod.Equal, "4"));
            }
        }
        [TestMethod]
        public void SingleWhereSQL()//获取一条数据
        {
            Account account1 = CreateDbBase().SingleOrDefault<Account>(" and id = @id", new { id = 4 });
            Account account2 = CreateDbBase().SingleOrDefault<Account>(" and id = 4");
            Assert.IsNotNull(account1);
        }
        [TestMethod]
        public void UpdateWhereSQL() //修改数据
        {
            //string s = "@ItemCategoryID";
            //string sql = "update ItemCategory set  ItemCategoryName=@ItemCategoryName where 1=1  and ItemCategoryID=@ItemCategoryID";

            //List<DbParameter> list = new List<DbParameter>()
            //{
            //    new SqlParameter("@ItemCategoryID",1001163),
            //    new SqlParameter("@ItemCategoryName","aaa"),
            //};
            //int d = CreateDbBase().DbConnecttion.Execute(sql, new { ItemCategoryName = "aaa", ItemCategoryID = 1001163 });

            ItemCategoryModel model = new ItemCategoryModel();
            model.ItemCategoryName = "aaa";
            //model.ItemCategoryID = 1001163;
            bool ret = CreateDbBase().Update<ItemCategoryModel>(model, " and ItemCategoryID=@ItemCategoryID", new { ItemCategoryID = 1001163 });
            ret = CreateDbBase().Update<ItemCategoryModel>(model, " and ItemCategoryID=1001163");
            ret = CreateDbBase().Update<ItemCategoryModel>(model, "");
        }
        [TestMethod]
        public void UpdateBatch()//批量修改
        {
            using (var db = CreateDbBase())
            {
                var model = db.SingleOrDefault<Account>(SqlQuery<Account>.Builder(db)
                   .AndWhere(m => m.Id, OperationMethod.Equal, "4"));
                model.Name = "李四";
                var result = db.Update<Account>(model, SqlQuery<Account>.Builder(db)
                   .AndWhere(m => m.Id, OperationMethod.In, new List<string>() { "5", "6" }));
            }
        }
        [TestMethod]
        public void Count()//数量
        {
            using (var db = CreateDbBase())
            {
                var result = db.Count<Account>(SqlQuery<Account>.Builder(db)
                   .AndWhere(m => m.Id, OperationMethod.In, new List<string>() { "5", "6" }));
                Console.WriteLine(result);
            }
        }
        [TestMethod]
        public void CountBySql()//数量
        {
            List<DbParameterModel> paramsList = new List<DbParameterModel>();
            paramsList.Add(new DbParameterModel() { ParameterName = "@ItemCategoryID" });

            var result = CreateDbBase().Count<ItemCategoryModel>(" and ItemCategoryID in (1001163,1001164)");
            result = CreateDbBase().Count<ItemCategoryModel>(" and ItemCategoryID in (1001163,1001164)");
            Console.WriteLine(result);
        }

        [TestMethod]
        public void Anonymous()
        {
            dynamic param = new { name = "aaa", id = 1 };

            List<DbParameterModel> list = new List<DbParameterModel>();
            Action<dynamic> singleParamAdd = (singleParam) =>
            {
                Type type = ((object)singleParam).GetType();
                foreach (PropertyInfo pro in type.GetProperties())
                {
                    DbParameterModel model = new DbParameterModel();
                    model.ParameterName = pro.Name;
                    model.Value = pro.GetValue(singleParam, null);
                    model.ParameterType = pro.GetType();
                    list.Add(model);
                }
            };

            IEnumerable multiExec = (object)param as IEnumerable;
            if (multiExec != null && !(multiExec is string))
            {
                foreach (var obj in multiExec)
                {
                    singleParamAdd(obj);
                }
            }
            if ((object)param != null)
            {
                singleParamAdd(param);
            }
        }


        [TestMethod]
        public void TestConvert()
        {
            for (int i = 0; i < 10; i++)
            {
                Stopwatch sw = new Stopwatch();

                List<UserGroupMenuModel> list = null;
                IDataReader dataReader = null;
                string sql = @"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
                    and UserGroupID in (" + 14 + ") and m.MenuType = 1";
                using (SqlConnection sqlConn = new SqlConnection("Data source=.;Initial Catalog=GTraceSH;User ID=sa;Password=123456;pooling=true;min pool size=10;max pool size=100;Persist Security Info=true;"))
                {
                    using (IDbCommand dbCommand = sqlConn.CreateCommand())
                    {
                        sqlConn.Open();
                        dbCommand.CommandText = sql;
                        dbCommand.CommandType = CommandType.Text;
                        dataReader = dbCommand.ExecuteReader();

                        sw.Start();
                        list = dataReader.ToList<UserGroupMenuModel>();
                        sw.Stop();
                        System.Diagnostics.Debug.WriteLine("1----查询总数:" + list.Count + " ;  ---" + sw.ElapsedMilliseconds);

                        sw.Restart();
                        //list = EntityConverter.ToList<UserGroupMenuModel>(dataReader);
                        sw.Stop();
                        System.Diagnostics.Debug.WriteLine("3----查询总数:" + list.Count + " ;  ---" + sw.ElapsedMilliseconds);
                    }
                }
            }


            Stopwatch sw1 = new Stopwatch();
            sw1.Start();
            List<UserGroupMenuModel> vList = CreateDbBase().DbConnecttion.Query<UserGroupMenuModel, MenuModel, UserGroupMenuModel>(@"select * from UserGroupMenu u ,Menu m where u.MenuID = m.MenuID
                    and UserGroupID in (" + 14 + ") and m.MenuType = 1", (userGroupMenu, menu) => { return userGroupMenu; }, splitOn: "MenuID").ToList();

            sw1.Stop();
            System.Diagnostics.Debug.WriteLine("2----查询总数:" + vList.Count + " ;  ---" + sw1.ElapsedMilliseconds);

        }

    }

}
